
const mssql =  require('../lib/mssql')
const sqlite =  require('../lib/sqlite')
const tables =  require('../config/tables')
const sql = require('sql');
const pinyin = require("node-pinyin");




class ArchivesController {

  static async list(ctx){
    sql.setDialect('sqlite');
    let where;
    let column;
    if(ctx.query.column){
        if(ctx.query.column instanceof Array){
            ctx.query.column.forEach(function(data){
                if(column){
                    column = column.or(tables.pinyin.column.equals(data));
                }else{
                     column = tables.pinyin.column.equals(data);
                }
            });
           
        }else{
            column = tables.pinyin.column.equals(ctx.query.column);
        }
        if(column){
            if(where){
                where = where.and(column);
            }else{
                where = column;
            }
        }
    }


    let area;
    if(ctx.query.area){
        if(ctx.query.area instanceof Array){
            ctx.query.area.forEach(function(data){
                if(area){
                    area = area.or(tables.pinyin.area.equals(data));
                }else{
                     area = tables.pinyin.area.equals(data);
                }
            });
           
        }else{
            area = tables.pinyin.area.equals(ctx.query.area);
        }
        if(area){
            if(where){
                where = where.and(area);
            }else{
                where = area;
            }
        }
    }
    if(ctx.query.abbr){
        if(where){
            where = where.and(tables.pinyin.abbr.like(ctx.query.abbr+"%"));
        }else{
            where = tables.pinyin.abbr.like(ctx.query.abbr+"%");
        }
       
    }


    let query =  tables.pinyin.select("*");
    if(where){
        query = query.where(where);
    }

    if(ctx.query.limit){
        query = query.limit(ctx.query.limit);
        if(ctx.query.offset){
            query = query.offset(ctx.query.offset);
        }
    }

    
  
    query = query.toQuery();
    ctx.body = await sqlite(query.text, query.values)
  }
  static async query(ctx){
    sql.setDialect('sqlite');
    let query =  tables.pinyin
        .where(
            tables.pinyin.abbr.like(ctx.query.abbr)
        ).toQuery();
        
    let py = await sqlite(query.text, query.values)
   
    ctx.body = py;
  }
  static async preprocessed(ctx){
    sql.setDialect('mssql');
    let query =  tables.archives
    .select(tables.archives.ID)
    .select(tables.archives.SN)
    .select(tables.archives.docName)
    .select(tables.archives.Area)
    .select(tables.archives.Column)
    .select(tables.archives.Layer)
    .select(tables.archives.Section)
    .toQuery();

    let result = await mssql(query.text, query.values)
    
    sql.setDialect('sqlite');
    let pinyin_drop = tables.pinyin.drop().ifExists().toQuery();
    let pinyin_create = tables.pinyin.create().ifNotExists().toQuery();
    
    await sqlite(pinyin_drop.text, pinyin_drop.values)
    await sqlite(pinyin_create.text, pinyin_create.values)

    result.forEach(async function(data){  


        const py = pinyin(data.docName, {
          style: "normal",
        });
        let abbr = "";
        py.forEach(function(data){
            abbr += data[0][0];
        });
        
        sql.setDialect('sqlite');
        let query = tables.pinyin.insert({
            "abbr":abbr, 
            "target_id":data.ID, 
            "sn":data.SN, 
            "doc_name":data.docName, 
            "area":data.Area, 
            "column":data.Column, 
            "layer":data.Layer, 
            "section":data.Section, 
        }).toQuery();
        let ret =  await sqlite(query.text, query.values)
        console.log(ret);
        
        
    });
    
    ctx.body = "ok";
  } 
 
}

module.exports = ArchivesController